import time

import math
import xlwt
from PyQt5.QtCore import QEvent, Qt, QThread, pyqtSignal
from PyQt5.QtGui import QIcon, QStandardItemModel, QStandardItem, QImage, QPixmap
from PyQt5.QtSql import QSqlQueryModel
from PyQt5.QtWidgets import QApplication, QWidget, QMessageBox, QHeaderView, QPushButton, \
    QAbstractItemView, QItemDelegate, QHBoxLayout, QFileDialog, QLabel
from datetime import datetime, timedelta
from PyQt5 import QtGui
from camera.MyCamera.MyCamera import MyMvCamera
from ui.index import Ui_index
from ui.showCount import Ui_showCount
from ui.showDetail import Ui_showDetail
from numpy import ndarray
import cv2
from Classify_frame import rcnn
import string
from ui.manualCheck import Ui_manualCheck
from ui.get_machine_code import Ui_Form
from ui.set_machine_code import Ui_setRegist
import base64
from pyDes import *
import os
import wmi
from PyQt5 import QtSql
from PyQt5.QtSql import QSqlQuery

from multiprocessing import process

check_str = string.ascii_letters + string.digits + "-_"
"""
数据库连接，目前使用sqlite数据库
数据库配置在config下面

"""
db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
if not os.path.isdir("./data/"):
    os.makedirs('./data/')
db.setDatabaseName("./data/data.db")
db.open()
query = QSqlQuery()

if db.open():
    """
    创建数据表
    """
    create_table_report = """CREATE TABLE if not exists  report (
							[id] INTEGER PRIMARY KEY AUTOINCREMENT,
							[report_number] TEXT  NOT NULL,
							[save_time] TimeStamp NOT NULL DEFAULT (datetime('now','localtime'))
							);
							"""

    create_table_count = """
							CREATE TABLE  if not exists count (
							id INTEGER PRIMARY KEY AUTOINCREMENT,
							num INT NOT NULL,
							remark CHAR(150),
							url CHAR(1000),
							report_id INTEGER NOT null,
							pattern INTEGER NOT null,
							FOREIGN KEY (report_id) REFERENCES report(id));
							"""
    query.exec_(create_table_report)
    query.exec_(create_table_count)


class showCountUi(QWidget, Ui_showCount):

    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.limit = 15  # 一页15条数据
        self.page = 1  # 初始页码
        self.total_page = 1  # 总页数
        self.dateTimeStart.setDateTime(datetime.now() - timedelta(days=1))  # 开始时间，当天时间的前一天
        self.dateTimeEnd.setDateTime(datetime.now())  # 结束时间，当前时间

        self.onDateTimeChanged()  # 初始化化表格数据
        # 当日期时间改变时触发槽函数
        self.showPage.setText("第{}/{}页".format(self.page, self.total_page))

        self.lineEdit.textChanged.connect(self.change_status)
        self.dateTimeStart.dateTimeChanged.connect(self.change_status)
        self.dateTimeEnd.dateTimeChanged.connect(self.change_status)

        self.pushButton_next_page.clicked.connect(self.next_page)
        self.pushButton_prev_page.clicked.connect(self.prev_page)

    def next_page(self):
        """
        下一页
        :return:
        """
        self.page += 1
        if self.page > self.total_page:
            self.page = self.total_page
        self.onDateTimeChanged()

    def prev_page(self):
        """
        上一页
        :return:
        """
        self.page -= 1
        if self.page <= 0:
            self.page = 1
        self.onDateTimeChanged()

    def change_status(self):
        """
        修改时间，和报告号时查询数据并展示
        :return:
        """
        self.page = 1
        self.total_page = 1
        self.onDateTimeChanged()

    def onDateTimeChanged(self):
        """
        检测时间，报告号变化，查询显示数据
        :return:
        """

        start_datetime = self.dateTimeStart.dateTime().toPyDateTime()
        end_datetime = self.dateTimeEnd.dateTime().toPyDateTime()

        select_report_sql_count = "select count(*) from report WHERE save_time BETWEEN '{}' AND  '{}' order by save_time DESC;".format(
            start_datetime, end_datetime)
        select_report_sql = "select * from report WHERE save_time BETWEEN '{}' AND  '{}'  order by save_time DESC  LIMIT {} OFFSET {};".format(
            start_datetime, end_datetime, self.limit, self.limit * (self.page - 1))

        if self.lineEdit.text():
            select_report_sql_count = "select count(*) from report WHERE report_number like '%{}%' and save_time BETWEEN '{}' AND  '{}' order by save_time DESC;".format(
                self.lineEdit.text(), start_datetime,
                end_datetime)
            select_report_sql = "select * from report WHERE report_number like '%{}%' and save_time BETWEEN '{}' AND  '{}'  order by save_time DESC  LIMIT {} OFFSET {};".format(
                self.lineEdit.text(), start_datetime,
                end_datetime, self.limit, self.limit * (self.page - 1))
        query.exec_(select_report_sql_count)
        query.next()
        total = query.record().value(0)  # 根据条件查询一共多少条数据
        self.total_page = int(total / self.limit) + 1  # 计算总页数
        self.showPage.setText("第{}/{}页".format(self.page, self.total_page))
        query.exec_(select_report_sql)

        # 表格显示数据
        self.model = QStandardItemModel(0, 3)
        self.model.setHorizontalHeaderLabels(['报告号', '计数时间', '查看详情'])
        row = 0
        while query.next():
            for col in range(0, query.record().count() - 1):
                i = QStandardItem(str(query.record().value(col + 1)))
                self.model.setItem(row, col, i)
            self.tableView.setItemDelegateForColumn(2, ButtonDelegate(self, self.tableView))  # 添加详情和报告下载按钮
            row += 1
        self.tableView.setModel(self.model)
        self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)  # 所有列自动拉伸，充满界面
        self.tableView.setEditTriggers(QAbstractItemView.NoEditTriggers)  # 不允许表格修改

    def button_detail_clicked(self, report_number):
        """查看详情"""
        self.show_detail = showDetailUi(report_number)
        self.show_detail.show()

    def button_load_clicked(self, report_number):
        """
        下载报告，生成excel表
        :param report_number: 报告编号
        :return:
        """
        base_dir = QFileDialog.getExistingDirectory()  # 返回选中的文件夹路径
        if not base_dir:
            QMessageBox.information(self, "提示", "请选择保存报告文件夹")
            return
        print("下载报告:报告号=%s" % report_number)

        # self.file_path = QFileDialog.getExistingDirectory(self, "请选择保存报告路径")
        # if not self.file_path:
        # 	return
        select_sql = "select save_time from report where report_number='%s';" % report_number
        query.exec_(select_sql)
        query.next()
        save_time = list(query.record().value(0))

        # 按“output/日期-AM/报告号.xlsx“或者“output/日期-PM/报告号.xlsx“保存报告
        if int(save_time[11] + save_time[12]) >= 12:
            self.file_path = os.path.join(base_dir, 'output/{}-PM'.format(''.join(save_time[:10])))
        # print(self.file_path)
        else:
            self.file_path = os.path.join(base_dir, 'output/{}-AM'.format(''.join(save_time[:10])))
        # print(self.file_path)

        if not os.access(self.file_path, 0):
            os.makedirs(self.file_path)

        select_sql = "select  pattern  from  count where report_id = (select id from report where report_number='%s');" % report_number
        query.exec_(select_sql)
        query.next()
        pattern = query.record().value(0)

        select_sql = "select  *  from  count where report_id = (select id from report where report_number='%s');" % report_number
        query.exec_(select_sql)  # 查询

        if pattern == 0:
            workbook = xlwt.Workbook(encoding='ascii')
            worksheet = workbook.add_sheet(report_number)  # 工作表
            worksheet.write(0, 0, label='ID')  # 表头
            worksheet.write(0, 1, label='菌落数')
            worksheet.write(0, 2, label='备注信息')
            worksheet.write(0, 3, label='报告号')

            row = 1
            while query.next():  # 读取数据写入表格
                col = 0
                for col in range(query.record().count() - 3):
                    # print(query.record().value(col))
                    worksheet.write(row, col, label=str(query.record().value(col)))
                    col = col
                worksheet.write(row, col + 1, label=str(report_number))
                row += 1
            workbook.save(self.file_path + "/%s.xlsx" % report_number)
        elif pattern == 1:
            workbook = xlwt.Workbook(encoding='ascii')
            worksheet = workbook.add_sheet(report_number)  # 工作表
            worksheet.write(0, 0, label='ID')  # 表头
            worksheet.write(0, 1, label='报告号')
            worksheet.write(0, 2, label='稀释倍数')
            alignment = xlwt.Alignment()  # Create Alignment
            alignment.horz = xlwt.Alignment.HORZ_CENTER
            style = xlwt.XFStyle()
            style.alignment = alignment
            worksheet.write_merge(0, 0, 3, 4, label='计数个数', style=style)

            row = 1  # 报告行数
            while query.next():  # 读取数据写入表格

                if row % 2 == 1:  # 奇数行
                    worksheet.write(math.ceil(row / 2), 0, label=str(query.record().value(0)))  # ID
                    worksheet.write(math.ceil(row / 2), 2, label=str(query.record().value(2)))  # 稀释倍数
                    worksheet.write(math.ceil(row / 2), 3, label=str(query.record().value(1)))  # 计数个数（1号位）
                    worksheet.write(math.ceil(row / 2), 1, label=str(report_number))  # 报告号
                    row += 1
                elif row % 2 == 0:  # 偶数行
                    worksheet.write(math.ceil(row / 2), 4, label=str(query.record().value(1)))  # 计数个数（2号位）
                    row += 1
            workbook.save(self.file_path + "/%s.xlsx" % report_number)
        QMessageBox.information(self, "提示", "下载成功，点击确定关闭提示窗口")


class ButtonDelegate(QItemDelegate):
    """
    报告里的按钮
    """

    def __init__(self, show_count, parent=None):
        super().__init__(parent=parent)
        self.show_count = show_count

    def paint(self, painter, option, index):
        if not self.parent().indexWidget(index):
            report_number = self.show_count.model.item(index.row(), 0).text()  # 获取报告号
            button_detail = QPushButton(
                self.tr('查看详情'),
                self.parent(),
                clicked=lambda: self.show_count.button_detail_clicked(report_number)
            )
            button_load = QPushButton(
                self.tr('下载报告'),
                self.parent(),
                clicked=lambda: self.show_count.button_load_clicked(report_number)
            )
            button_detail.index = [index.row(), index.column()]
            button_load.index = [index.row(), index.column()]
            h_box_layout = QHBoxLayout()
            h_box_layout.addWidget(button_detail)
            h_box_layout.addWidget(button_load)
            h_box_layout.setContentsMargins(0, 0, 0, 0)
            # h_box_layout.setAlignment(Qt.AlignCenter)
            widget = QWidget(self.parent())
            widget.setLayout(h_box_layout)
            self.parent().setIndexWidget(index, widget)


class ButtonShowImage(QItemDelegate):
    """
    报告里的按钮
    """

    def __init__(self, show_image, parent=None):
        super().__init__(parent=parent)
        self.show_image = show_image

    def paint(self, painter, option, index):
        if not self.parent().indexWidget(index):
            num = self.show_image.model.item(index.row(), 0).text()  # 数量
            remark = self.show_image.model.item(index.row(), 1).text()  # 备注
            url = self.show_image.model.item(index.row(), 2).text()  # 路径
            button_detail = QPushButton(
                self.tr('点击查看'),
                self.parent(),
                clicked=lambda: self.show_image.show_big_image(num, remark, url, index.row())
            )

            button_detail.index = [index.row(), index.column()]
            h_box_layout = QHBoxLayout()
            h_box_layout.addWidget(button_detail)
            h_box_layout.setContentsMargins(0, 0, 0, 0)
            # h_box_layout.setAlignment(Qt.AlignCenter)
            widget = QWidget(self.parent())
            widget.setLayout(h_box_layout)
            self.parent().setIndexWidget(index, widget)


class showDetailUi(QWidget, Ui_showDetail):
    def __init__(self, report_number):
        super().__init__()
        self.setupUi(self)
        self.report_number = report_number
        self.pushButton_report_number.setText("报告号：%s" % self.report_number)
        self.pushButton_number_str = "数量："
        self.pushButton_remark_str = "备注："
        self.pushButton_time_str = "计数时间："
        self.show_image_info()

    def show_image_info(self):
        """显示图片"""

        sql = "select save_time from report where report_number = '%s';" % self.report_number
        query.exec_(sql)
        query.next()
        self.pushButton_time_str += str(query.record().value(0))
        self.pushButton_time.setText(self.pushButton_time_str)

        # 查询到数据
        select_sql = "select  *  from  count where report_id = (select id from report where report_number='%s');" % self.report_number
        query.exec_(select_sql)

        # 表格显示数据
        self.model = QStandardItemModel(0, 4)
        self.model.setHorizontalHeaderLabels(['数量', '备注', '图片保存路径', '操作'])
        row = 0
        while query.next():
            for col in range(0, query.record().count() - 2):
                i = QStandardItem(str(query.record().value(col + 1)))

                self.model.setItem(row, col, i)
            self.tableView.setItemDelegateForColumn(3, ButtonShowImage(self, self.tableView))  # 添加详情和报告下载按钮
            row += 1
        self.tableView.setModel(self.model)
        self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)  # 所有列自动拉伸，充满界面
        self.tableView.setEditTriggers(QAbstractItemView.NoEditTriggers)  # 不允许表格修改

    def show_big_image(self, num, remark, url, row):
        """显示大图"""
        self.pushButton_number.setText(self.pushButton_number_str + str(num))
        self.pushButton_remark.setText(self.pushButton_remark_str + str(remark))

        if os.access(url, 0) == 0:
            self.label.setText("图片获取失败")
            return
        img = cv2.imread(url)
        img2 = img.copy()
        b, g, r = cv2.split(img)
        img = cv2.merge((r, g, b))  # opencv读取图片通道为 bgr，修改成rgb

        height, width, bytesPerComponent = img.shape
        bytesPerLine = bytesPerComponent * width
        q_image = QImage(img, width, height, bytesPerLine, QImage.Format_RGB888).scaled(width, height)  # 读取图片
        self.label.setPixmap(QPixmap.fromImage(q_image))


class MyUi(QWidget, Ui_index):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.camera = None  # 相机
        self.com = None  # 串口
        self.hover_mouse = None  # 按下鼠标
        self.remarks = {}  # 备注信息
        self.report_number_str = None  # 报告号
        self.report_number_id = None  # 报告id
        self.data_list = []  # 保存四个培养皿的结果
        self.last_time_move_y = 0
        self.last_time_move_x = 0
        self.scroll_bar_y = self.scrollArea_2.verticalScrollBar()
        self.scroll_bar_x = self.scrollArea_2.horizontalScrollBar()
        self.label.installEventFilter(self)
        self.pushButton_start.clicked.connect(self.start_func)
        self.pushButton_report.clicked.connect(self.report_func)
        self.frame_rate = 50
        self.init_db()
        # self.rcnn = rcnn()
        self.camera = MyMvCamera()
        self.show_video = ShowVideoThread(self)
        self.show_video.start()
        self.start_success()
        pb_dir = './jljs1.pb'
        self.use_object_detect_pb = rcnn(pb_dir)
        self.comboBox.currentIndexChanged.connect(self.pattern_changed)
        self.dilute_1.setEnabled(False)
        self.dilute_2.setEnabled(False)
        self.dilute_3.setEnabled(False)
        self.divide.setChecked(True)

    # 模式切换
    def pattern_changed(self):
        # 模式A
        if self.comboBox.currentIndex() == 0:
            self.dilute_1.setEnabled(False)
            self.dilute_2.setEnabled(False)
            self.dilute_3.setEnabled(False)
        # 模式B
        elif self.comboBox.currentIndex() == 1:
            self.dilute_1.setEnabled(True)
            self.dilute_2.setEnabled(True)
            self.dilute_3.setEnabled(True)

    def start_success(self):
        """
        标记启动界面成功
        """
        if not os.path.isdir("startflag/"):
            os.makedirs("startflag/")
        with open("startflag/success.txt", 'w') as f:
            f.write('')

    def init_db(self):
        """
        初始化数据库连接
        :return:
        """
        if not db.open():
            QMessageBox.information(self, "提示", "数据库连接失败，请检查数据库配置")

    def start_func(self):
        """
        点击开始计数
        :return:
        """
        self.pattern = self.comboBox.currentIndex()
        # if self.pattern == 1 and self.dilute.text() == '':
        # 	QMessageBox.information(self,'提示','请输入稀释倍数')
        # 	return
        self.data_list.clear()
        self.pushButton_start.setEnabled(False)
        self.pushButton_start.setText("计算中...")

        # self.dilution = self.dilute.text()

        if not self.check_report_number():
            self.refresh_button()
            return
        # 模式A
        if self.pattern == 0:
            self.get_remarks()  # 获取备注信息
        # 模式B
        else:
            self.remarks[0] = self.dilute_1.text()
            self.remarks[1] = self.dilute_1.text()
            self.remarks[2] = self.dilute_2.text()
            self.remarks[3] = self.dilute_2.text()
            self.remarks[4] = self.dilute_3.text()
            self.remarks[5] = self.dilute_3.text()

        while True:
            ok, img_buff = self.camera.get_grabbing()  # 获取流，如果ok 返回True 表示获取成功，img_buff为图像帧
            if not isinstance(img_buff, ndarray):
                QMessageBox.information(self, "错误", "获取流失败，请检查相机是否正常连接")
                self.refresh_button()
                return
            if ok:
                b, g, r = cv2.split(img_buff)
                img_buff = cv2.merge((r, g, b))
                break
        # img_buff = cv2.imread("6_at_a_time_count.bmp")  # 测试某个图片
        # cv2.imwrite('storage/asdassdasd.jpg', img_buff)  # 保存原图
        # print('OK')
        result = []
        num = 0
        for image in self.cut_image(img_buff):  # 切割

            cv2.imwrite('storage/{}.jpg'.format(math.ceil(time.time())), image)  # 保存原图
            # print(image.shape)

            total, file_path = self.run_rcnn(image, self.remarks[num])  # 目标检测
            self.data_list.append([total, self.remarks[num], file_path, self.pattern])  # 保存结果
            result.append([total, self.remarks[num], file_path, self.pattern])
            num += 1
        self.refresh_button()

        if self.checkBox.isChecked():  # 勾选实时查看,实时查看可以修改计数
            self.now_show_image()
        else:
            for data in result:
                self.insert_result(*data)  # 将结果保存到数据库，不是实时查看直接存数据库
            QMessageBox.information(self, "提示", "计数结束，数据存储成功")

    def refresh_button(self):
        """
        刷新button按钮
        :return:
        """
        self.pushButton_start.setText("开始计数")
        self.pushButton_start.setEnabled(True)

    def run_rcnn(self, img, remark):
        """
        运行目标检测算法，检测菌落数量,接收一个原始菌落图片数据
        返回一个列表包含菌落个数以及检测完成打上标签的图片保存路径
        """

        # 调用算法识别菌落

        #
        bac1, bac2, bac3, box4 = self.use_object_detect_pb.get_box(img)
        total = 0
        # 分割
        if self.divide.isChecked():
            total += len(bac1) * 1
            total += len(bac2) * 2
            total += len(bac3) * 3
            print("菌落数量(分割)：%s" % total)
            for j in range(len(bac1)):
                box = bac1[j]
                img = cv2.rectangle(img, (box[0], box[1]), (box[2], box[3]), (0, 0, 255), 2)
            for j in range(len(bac2)):
                box = bac2[j]
                img = cv2.rectangle(img, (box[0], box[1]), (box[2], box[3]), (0, 255, 0), 2)
            for j in range(len(bac3)):
                box = bac3[j]
                img = cv2.rectangle(img, (box[0], box[1]), (box[2], box[3]), (255, 0, 0), 2)
        # 不分割
        else:
            total += len(bac1) * 1
            total += len(bac2) * 1
            total += len(bac3) * 1
            print("菌落数量(不分割)：%s" % total)
            for j in range(len(bac1)):
                box = bac1[j]
                img = cv2.rectangle(img, (box[0], box[1]), (box[2], box[3]), (0, 0, 255), 2)
            for j in range(len(bac2)):
                box = bac2[j]
                img = cv2.rectangle(img, (box[0], box[1]), (box[2], box[3]), (0, 0, 255), 2)
            for j in range(len(bac3)):
                box = bac3[j]
                img = cv2.rectangle(img, (box[0], box[1]), (box[2], box[3]), (0, 0, 255), 2)

        base_path = "out_img/{}".format(self.report_number_str)
        if not os.path.isdir(base_path):
            os.makedirs(base_path)
        # print('bast_path:{}'.format(base_path))
        name = remark + ".jpg"
        file_path = base_path + "/{}".format(name)
        if os.path.isfile(file_path):
            file_path = file_path[:-4] + '_' + str(int(time.time())) + '.jpg'
        cv2.imencode('.jpg', img)[1].tofile(file_path)
        return total, file_path

    def cut_image(self, image):
        """
        接收一个cv2.imread()读取的图片数据
        图片切割，将四个培养皿切割成单个培养皿
        :return:
        """
        height = image.shape[1]
        width = image.shape[0]
        item_width = math.ceil(width / 2)
        item_height = math.ceil(height / 3)
        image_list = []

        tt = 0
        # 切成6块
        for j in range(0, 3):  # 基于原图的位置切割
            for i in range(0, 2):
                croped = image[i * item_width:(i + 1) * item_width, j * item_height:(j + 1) * item_height]
                cv2.imwrite('test/{}.jpg'.format(tt), croped)
                tt += 1
                image_list.append(croped)

        # 提取培养皿边缘并以最小外接矩形切割
        for images in image_list:
            img = cv2.Canny(images, 200, 600)  # Canny检测边缘
            # ret, thresh = cv2.threshold(img, 0, 255, cv2.THRESH_BINARY + cv2.THRESH_OTSU)  # 大津阈值二值化
            contours = cv2.findContours(img, cv2.RETR_EXTERNAL,
                                        cv2.CHAIN_APPROX_SIMPLE)  # RETR_EXTERNAL定义只检测外围轮廓,simple只测1个轮廓
            cnts = contours[0]
            rected = False  # 保证每次调用只切1个培养皿大小的图
            for cnt in cnts:
                # 外接矩形框
                x, y, w, h = cv2.boundingRect(cnt)

                if w > 800 and h > 800 and rected == False:
                    # 计算最小封闭圆形的中心和半径
                    (x_center, y_center), radius = cv2.minEnclosingCircle(cnt)
                    # 转换成整数
                    center = (int(x_center), int(y_center))
                    radius = int(radius)

                    if radius >= 400 and radius <= 500:
                        img_new = images.copy()
                        img_new[:, :] = 255
                        img_new = cv2.circle(img_new, center, radius, (0, 0, 0), -1)  # 白底黑圆
                        img_new = cv2.bitwise_or(img_new, images)  # 或操作，把圆外（培养皿外）的噪声去除
                        img_cropped = img_new[y:y + h, x:x + w]

                        yield img_cropped
                        rected = True

    def now_show_image(self):
        """实时查看图片"""
        self.manual_check = ManualCheck(self.data_list, self.report_number_str, self.pattern)

        self.manual_check.show()

    def report_func(self):
        """
        显示查看报告页
        :return:
        """
        self.show_count = showCountUi()
        self.show_count.show()

    def check_report_number(self):
        """
        校验报告编号正确性,并将报告写入到数据库
        :return:
        """
        self.report_number_str = self.report_number.text()
        if not self.report_number_str:
            res = QMessageBox.information(self, "提示", "未填写报告号，将自动生成", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)
            if res == QMessageBox.Yes:
                self.report_number_str = datetime.now().strftime('%Y%m%d%H%M%S')
            else:
                self.pushButton_start.setEnabled(True)
                return False

        for i in self.report_number_str:
            if i not in check_str:
                QMessageBox.information(self, "提示", "报告号只能是数字，字母或下划线组成")
                return False
        # 校验报告号是否存在
        query_sql = "select * from report where report_number='%s';" % self.report_number_str
        query_result = self.SerializeData(query_sql)
        if query_result:
            res = QMessageBox.information(self, "提示", "报告号存在，将结果保存到同一个报告", QMessageBox.Yes | QMessageBox.No,
                                          QMessageBox.Yes)
            if res == QMessageBox.Yes:
                self.report_number_str = query_result[0][1]
                self.report_number_id = query_result[0][0]
                return True
            else:
                return False
        # 将报告号写入数据库
        insert_sql = "insert into report (report_number) values ('{}');".format(self.report_number_str)
        if query.exec_(insert_sql):
            query.exec_("SELECT LAST_INSERT_ROWID();")
            query.next()
            self.report_number_id = query.record().value(0)
            print("写入报告号成功,ID:=%s" % self.report_number_id)
            db.commit()
            return True
        print("写入报告号异常")
        return False

    def insert_result(self, number, remark, url, pattern):
        """
        保存计数结果
        :param number: 结果
        :param remark: 某个培养皿的备注
        :param url: 计算结束后打上标签的路径
        :return:
        """
        insert_sql = "insert into main.count values (NULL ,{},'{}','{}',{},{});".format(
            number, remark, url, self.report_number_id, pattern)
        if query.exec_(insert_sql):
            print("结果写入成功")
            return True
        print("写入失败")
        return False

    def eventFilter(self, source, event):
        """
        点击视频拖动查看
        :param source:拖动源控件
        :param event:事件
        :return:
        """
        if event.type() == QEvent.MouseButtonPress:
            self.hover_mouse = True
        if event.type() == QEvent.MouseMove:
            if self.hover_mouse:
                if self.last_time_move_y == 0:
                    self.last_time_move_y = event.pos().y()
                if self.last_time_move_x == 0:
                    self.last_time_move_x = event.pos().x()
                y = self.last_time_move_y - event.pos().y()
                x = self.last_time_move_x - event.pos().x()
                self.scroll_bar_y.setValue(self.scroll_bar_y.value() + y)
                self.scroll_bar_x.setValue(self.scroll_bar_x.value() + x)
                self.last_time_move_y = event.pos().y()
                self.last_time_move_x = event.pos().x()
        elif event.type() == QEvent.MouseButtonRelease:
            self.hover_mouse = False
            self.last_time_move_y = 0
            self.last_time_move_x = 0
        return QWidget.eventFilter(self, source, event)

    def get_remarks(self):
        """
        获取备注信息,如果没有则使用时间戳标识不同位置的培养皿
        :return:
        """
        line_1 = self.lineEdit_1.text()
        line_2 = self.lineEdit_2.text()
        line_3 = self.lineEdit_3.text()
        line_4 = self.lineEdit_4.text()
        line_5 = self.lineEdit_5.text()
        line_6 = self.lineEdit_6.text()

        time_str = str(int(time.time()))
        if len(line_1) > 100 or len(line_2) > 100 or len(line_3) > 100 or len(line_4) > 100 or len(line_5) > 100 or len(
                line_6) > 100:
            QMessageBox.information(self, "提示", "备注信息过长,将自动使用时间戳生成备注信息")
            line_1, line_2, line_3, line_4, line_5, line_6 = time_str, time_str, time_str, time_str, time_str, time_str
        else:
            for i in line_1:
                if i not in check_str:
                    QMessageBox.information(self, "提示", "培养皿1备注只能是数字，字母或下划线组成,将自动使用时间戳生成备注信息")
                    line_1 = time_str
                    break
            for i in line_2:
                if i not in check_str:
                    QMessageBox.information(self, "提示", "培养皿2备注只能是数字，字母或下划线组成,将自动使用时间戳生成备注信息")
                    line_2 = time_str
                    break
            for i in line_3:
                if i not in check_str:
                    QMessageBox.information(self, "提示", "培养皿3备注只能是数字，字母或下划线组成,将自动使用时间戳生成备注信息")
                    line_3 = time_str
                    break
            for i in line_4:
                if i not in check_str:
                    QMessageBox.information(self, "提示", "培养皿4备注只能是数字，字母或下划线组成,将自动使用时间戳生成备注信息")
                    line_4 = time_str
                    break
            for i in line_5:
                if i not in check_str:
                    QMessageBox.information(self, "提示", "培养皿5备注只能是数字，字母或下划线组成,将自动使用时间戳生成备注信息")
                    line_5 = time_str
                    break
            for i in line_6:
                if i not in check_str:
                    QMessageBox.information(self, "提示", "培养皿6备注只能是数字，字母或下划线组成,将自动使用时间戳生成备注信息")
                    line_6 = time_str
                    break
        self.remarks[0] = line_1 + "_1" if line_1 else time_str + "_1"
        self.remarks[1] = line_2 + "_2" if line_2 else time_str + "_2"
        self.remarks[2] = line_3 + "_3" if line_3 else time_str + "_3"
        self.remarks[3] = line_4 + "_4" if line_4 else time_str + "_4"
        self.remarks[4] = line_5 + "_5" if line_5 else time_str + "_5"
        self.remarks[5] = line_6 + "_6" if line_6 else time_str + "_6"

    def SerializeData(self, str_query):
        """
        对查询的数据进行序列化，返回列表。
        :param str_query: 查询语句
        :return: 数据列表
        """
        sqlModel = QSqlQueryModel()
        sqlModel.fetchMore()
        sqlModel.setQuery(str_query)
        res = []
        for nProvinceNum in range(sqlModel.rowCount()):
            res.append([sqlModel.record(nProvinceNum).value(i) for i in range(sqlModel.record(nProvinceNum).count())])
        return res

    def closeEvent(self, a0: QtGui.QCloseEvent) -> None:  # 关闭事件
        if db.open():
            db.close()
        if self.camera:
            self.camera.closeCamera()
        if os.path.isfile("./startflag/success.txt"):
            os.remove("./startflag/success.txt")
        sys.exit(0)


class ShowVideoThread(QThread):
    """
    显示视频
    """

    def __init__(self, main):
        super(ShowVideoThread, self).__init__()
        self.main = main

    def run(self) -> None:
        while True:
            if not self.main.camera.init_tag:
                return
            ok, img_buff = self.main.camera.get_grabbing()
            if ok:
                cv2.circle(img_buff, (525, 470), 415, (255, 0, 0), 1)
                cv2.circle(img_buff, (525, 1500), 415, (255, 0, 0), 1)  # 视频上方的六个圈
                cv2.circle(img_buff, (1530, 470), 415, (255, 0, 0), 1)
                cv2.circle(img_buff, (1530, 1500), 415, (255, 0, 0), 1)
                cv2.circle(img_buff, (2530, 1500), 415, (255, 0, 0), 1)
                cv2.circle(img_buff, (2530, 470), 415, (255, 0, 0), 1)
                height, width, bytesPerComponent = img_buff.shape
                bytesPerLine = bytesPerComponent * width
                q_image = QImage(img_buff, width, height, bytesPerLine,
                                 QImage.Format_RGB888).scaled(self.main.label.width(), self.main.label.height())

                self.main.label.setPixmap(QPixmap.fromImage(q_image))

            time.sleep(self.main.frame_rate / 1000)


class ManualCheck(QWidget, Ui_manualCheck):
    def __init__(self, data_list, report_number, pattern):
        super(ManualCheck, self).__init__()
        self.setupUi(self)
        self.setWindowFlags(Qt.WindowStaysOnTopHint)
        self.showMaximized()

        # 自定义 label
        self.label = MyQLabel(self.scrollAreaWidgetContents_2)
        self.label.setAlignment(Qt.AlignCenter)
        self.gridLayout_5.addWidget(self.label, 0, 0, 1, 1)

        self.report_number = report_number
        self.data_list = data_list
        self.pattern = pattern
        self.number_str = "数量："
        self.remark_str = "备注："
        self.time_str = "计数时间："
        self.num = None  # 计数量
        self.remark = None  # 备注
        self.url = None  # 当前处理中的图片url
        self.row = None  # 行索引
        self.report_number_id = None  # 报告id
        self.prev_img_url = None  # 上一次处理的图片url
        self.big_image = False  # 标记是否显示大图
        self.pushButton_report_number.setText("报告号：%s" % self.report_number)
        self.img = None  # 处理中的图片
        self.show_image_info()
        self.label.qrect_signal.connect(self.update_image)
        self.label.right_signal.connect(self.sub_num)

    def show_image_info(self):
        """显示图片"""

        sql = "select save_time,id from report where report_number = '%s';" % self.report_number
        query.exec_(sql)
        query.next()
        self.time_str += str(query.record().value(0))
        self.pushButton_time.setText(self.time_str)
        self.report_number_id = query.record().value(1)

        # 表格显示数据
        self.model = QStandardItemModel(0, 4)
        self.model.setHorizontalHeaderLabels(['数量', '备注', '图片保存路径', '操作'])

        for row, one_data in enumerate(self.data_list):
            for col, data in enumerate(one_data):
                i = QStandardItem(str(data))
                if col != 4:
                    self.model.setItem(row, col, i)
            self.tableView.setItemDelegateForColumn(3, ButtonShowImage(self, self.tableView))  # 添加详情和报告下载按钮

        self.tableView.setModel(self.model)
        self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)  # 所有列自动拉伸，充满界面
        self.tableView.setEditTriggers(QAbstractItemView.NoEditTriggers)  # 不允许表格修改

    def show_big_image(self, num, remark, url, row):
        """显示大图"""
        self.big_image = True
        self.num = int(num)
        self.remark = remark
        self.url = url
        self.row = row
        self.pushButton_number.setText(self.number_str + str(self.num))
        self.pushButton_remark.setText(self.remark_str + str(remark))

        if os.access(url, 0) == 0:
            self.label.setText("图片获取失败")
            return
        self.save_update_image(url)  # 保存上一次处理的图片

        self.img = cv2.imread(url)
        b, g, r = cv2.split(self.img)
        self.img = cv2.merge((r, g, b))  # opencv读取图片通道为 bgr，修改成rgb

        height, width, bytesPerComponent = self.img.shape
        self.label.setFixedSize(width, height)
        self.label.setAlignment(Qt.AlignCenter)

        bytesPerLine = bytesPerComponent * width
        q_image = QImage(self.img, width, height, bytesPerLine, QImage.Format_RGB888).scaled(self.label.width(),
                                                                                             self.label.height())  # 读取图片

        self.label.setPixmap(QPixmap.fromImage(q_image))

    def save_update_image(self, url):
        if self.prev_img_url:  # 保存上一个处理完的图片
            r, g, b = cv2.split(self.img)
            self.img = cv2.merge((b, g, r))
            cv2.imwrite(self.prev_img_url, self.img)
        self.prev_img_url = url  # 保存完后重置为当前的图片路径

    def update_image(self, qrect_list):
        """
        更新鼠标计数的点
        :param qrect_list:
        :return:
        """
        if not self.big_image:
            return
        height, width, bytesPerComponent = self.img.shape
        bytesPerLine = bytesPerComponent * width
        cv2.circle(self.img, qrect_list, 5, (255, 0, 0), -1)
        q_image = QImage(self.img, width, height, bytesPerLine, QImage.Format_RGB888).scaled(self.label.width(),
                                                                                             self.label.height())  # 读取图片
        self.label.setPixmap(QPixmap.fromImage(q_image))
        self.num += 1
        self.pushButton_number.setText(self.number_str + str(self.num))
        self.model.setItem(int(self.row), 0, QStandardItem(str(self.num)))

    def sub_num(self, right_list):
        """
        按鼠标右键，数量减1
        :return:
        """
        if not self.big_image:
            return
        height, width, bytesPerComponent = self.img.shape
        bytesPerLine = bytesPerComponent * width
        cv2.circle(self.img, right_list, 5, (255, 255, 255), -1)
        q_image = QImage(self.img, width, height, bytesPerLine, QImage.Format_RGB888).scaled(self.label.width(),
                                                                                             self.label.height())  # 读取图片
        self.label.setPixmap(QPixmap.fromImage(q_image))
        self.num -= 1
        self.pushButton_number.setText(self.number_str + str(self.num))
        self.model.setItem(int(self.row), 0, QStandardItem(str(self.num)))

    def save_data(self):
        """从表格里读取数据写入到数据库"""
        try:
            row_data = []
            for row in range(self.model.rowCount()):
                for col in range(self.model.columnCount() - 1):  # 读取表格的数据
                    row_data.append(self.model.item(row, col).text())
                row_data.append(self.pattern)
                self.insert_result(*row_data)  # 保存数据库
                row_data.clear()
        except Exception as e:
            print(e)

    def closeEvent(self, a0: QtGui.QCloseEvent) -> None:
        self.save_data()  # 保存数据到数据库
        self.save_update_image(self.prev_img_url)  # 关闭窗口在调用一次保存图片，这样才能将最后处理的一张图片保存

    def insert_result(self, number, remark, url, pattern):
        """
        保存计数结果
        :param number: 结果
        :param remark: 某个培养皿的备注
        :param url: 计算结束后打上标签的路径
        :return:
        """
        insert_sql = "insert into main.count values (NULL ,{},'{}','{}',{},{});".format(
            number, remark, url, self.report_number_id, pattern)
        if query.exec_(insert_sql):
            print("结果写入成功")
            return True
        print("写入失败")
        return False


class MyQLabel(QLabel):
    """重写Qlabel用于画图"""

    x0 = 0
    y0 = 0

    qrect_signal = pyqtSignal(tuple)
    right_signal = pyqtSignal(tuple)

    # 鼠标点击事件
    def mousePressEvent(self, event):

        if event.buttons() == Qt.LeftButton:
            self.x0 = event.x()
            self.y0 = event.y()
            self.qrect_signal.emit((self.x0, self.y0))
        if event.buttons() == Qt.RightButton:
            self.x0 = event.x()
            self.y0 = event.y()
            self.right_signal.emit((self.x0, self.y0))


class GetMachineCode(QWidget, Ui_Form):
    """
    提示获取机器码窗口
    """

    def __init__(self):
        super(GetMachineCode, self).__init__()
        self.setupUi(self)
        ok, err = check_func()  # 验证注册码是否有效
        if ok:
            sys.exit()
        machine_code = get_machine_code()
        self.textEdit.setText(machine_code)
        self.pushButton_2.clicked.connect(self.get_button)
        self.pushButton.clicked.connect(self.copy_machine_code)
        self.pushButton_3.clicked.connect(self.set_machine_code)

    def get_button(self):
        """点击获取机器码"""
        self.textEdit.setText(get_machine_code())

    def set_machine_code(self):
        """设置注册码"""
        self.set_win = SetMachineCode()
        self.set_win.close_signal.connect(self.start_up)
        self.set_win.show()
        self.hide()

    def copy_machine_code(self):
        """将机器码复制到粘贴板"""
        code = get_machine_code()
        clipboard = QApplication.clipboard()
        clipboard.setText(code)

    def start_up(self):
        """
        当设置注册码成功后关闭窗口再次交易注册码是否正确
        如果正确将打开软件
        否则提示注册码错误
        :return:
        """
        ok, errmsg = check_func()
        if not ok:
            QMessageBox.information(self, "错误", "注册码错误，请确认后重新输入")
            self.show()
            return

        # 保存注册码并验证通过后打开主程序
        window = MyUi()
        window.showMaximized()  # 最大化
        window.setStyleSheet(qss)  # qss样式
        window.show()
        self.close()


class SetMachineCode(QWidget, Ui_setRegist):
    """
    设置注册码窗口
    """
    close_signal = pyqtSignal()

    def __init__(self):
        super(SetMachineCode, self).__init__()
        self.setupUi(self)
        self.pushButton.clicked.connect(self.save)

    def save(self):
        if not self.textEdit.toPlainText():
            QMessageBox.information(self, "ERROR", "请输入注册码")
            return
        code = self.textEdit.toPlainText()
        with open("licenses.txt", 'w') as f:
            f.write(code)
        res = QMessageBox.information(self, "提示", "注册码保存成功", QMessageBox.Close)
        if res == QMessageBox.Close:
            self.close_signal.emit()
            self.close()


# 窗口简单美化
qss = """
QLineEdit {
    border: 1px solid rgb(41, 57, 85);
    border-radius: 3px;
    background: white;  
    selection-background-color: green;
    font-size: 14px ;
}

QLineEdit:hover {
    border: 1px solid blue;
}


QWidget{background-color:#FFDAB9;}

"""


def check_func():
    """
    校验验证码是否正确
    返回True 注册码正确，返回False注册码失败
    """
    if not os.path.isfile("./licenses.txt"):
        return False, "licenses文件不存在"
    with open("./licenses.txt") as f:
        code = f.read()
    if not code:
        return False, "注册码不存在"
    de_code = des_decrypt(code)
    if de_code == get_machine_code():
        return True, "校验成功"
    else:
        return False, "校验失败"


def des_decrypt(str):
    """解码"""
    Des_Key = "BHC#@*U6"
    Des_IV = b"\x24\x33\x37\x82\xBC\x68\x5A\xE7"
    try:
        EncryptStr = base64.b64decode(str)
        k = des(Des_Key, CBC, Des_IV, pad=None, padmode=PAD_PKCS5)
        DecryptStr = k.decrypt(EncryptStr)
    except Exception as e:
        return False
    return DecryptStr.decode()


def get_machine_code():
    """
    获取机器码
    :return:
    """
    w = wmi.WMI()
    s = ""
    for disk in w.Win32_DiskDrive()[:1]:
        s += disk.qualifiers.get('UUID').strip("{}")
        s += disk.SerialNumber.strip(" ")
    for bios_id in w.Win32_BIOS():
        s += "".join([str(i) for i in bios_id.BiosCharacteristics])
    return s


def main():
    ok, errmsg = check_func()  # 验证注册码
    if not ok:
        app = QApplication(sys.argv)
        app.setWindowIcon(QIcon("./img/titleIcon.png"))
        window = GetMachineCode()
        window.show()
        sys.exit(app.exec_())

    else:
        app = QApplication(sys.argv)
        app.setWindowIcon(QIcon("./img/titleIcon.png"))  # 图标
        window = MyUi()
        window.showMaximized()  # 最大化
        window.setStyleSheet(qss)  # qss样式
        window.show()
        sys.exit(app.exec_())


if __name__ == '__main__':
    app = QApplication(sys.argv)
    app.setWindowIcon(QIcon("./img/titleIcon.png"))  # 图标
    window = MyUi()
    window.showMaximized()  # 最大化
    # window.setStyleSheet(qss)  # qss样式
    window.show()
    sys.exit(app.exec_())
